package com.tygy.dao;

import com.tygy.doMatin.User;
import com.tygy.util.DbUtilHikari;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

import cn.hutool.crypto.SecureUtil;

public class UserDao {


    public int insertUser(String uname, String upsw){
        //1. 声明一些变量
        //数据库连接对象
        Connection conn = null;
        //预编译对象
        PreparedStatement pstmt = null;
        //注册成功与否的标志
        int flag = 0;
        //2. 获取数据库连接
        conn = DbUtilHikari.getConnection();

        //3. 定义并准备SQL语句
        String sql = "INSERT INTO user(username, userpsw) value(?, ?)";
        try {
            pstmt = conn.prepareStatement(sql);

            //给？赋值
            pstmt.setString(1,uname);
            pstmt.setString(2,SecureUtil.md5(upsw));

            //5. 执行SQL语句
            flag = pstmt.executeUpdate();

        } catch (SQLException e) {
            e.printStackTrace();
        }finally {
            //关闭数据库资源
            DbUtilHikari.close(pstmt,conn);
        }

        return flag;
    }

    /**
     * 按照用户名和密码进行登录：
     * @param uname 用户名
     * @param upsw 密码
     * @return 登录成功：返回true， 否则返回false
     */
    public boolean login(String uname, String upsw){
        //登录成功与否的标志：true表示登录成功
        boolean flag = false;

        //1. 连接准备
        //conn: 表示数据库连接
        Connection conn = null;
        //pstmt：执行SQL语句
        PreparedStatement pstmt = null;
        //rs代表结果集，从数据库表里查到的结果放在rs中
        ResultSet rs = null;

        //2. 获取数据库连接: 从hikari连接池中获取：不是直接从数据库中打开连接：
        //用连接池能够提高数据库访问性能，特别是在大并发场景下
        conn = DbUtilHikari.getConnection();

        //3. 定义且准备SQL语句
        String sql = "SELECT * FROM user WHERE username = ? AND userpsw = ?";
        try {
            pstmt = conn.prepareStatement(sql);

            //给？赋值
            pstmt.setString(1, uname);
            pstmt.setString(2, SecureUtil.md5(upsw));

            //下达执行指令：执行SQL语句: executeQuery:JDBC中执行查询的方法
            rs = pstmt.executeQuery();

            //判断rs结果集中有无数据：next():判断rs中有数据吗？有则返回true（即查到东西了）；
            if (rs.next()){
                flag = true;
            }else{
                flag = false;
            }
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            //关闭数据库连接
            DbUtilHikari.close(rs, pstmt,conn);
        }

        return flag;
    }

    /**
     * 按照用户标号删除用户
     * @param userid：用户编号
     * @return：如果删除成功返回1；否则返回0
     */
    public int deleteUser(int userid){

        int flag = 0;
        //变量声明
        Connection conn = null;
        PreparedStatement pstmt = null;

        //获取数据库连接
        conn = DbUtilHikari.getConnection();

        try {
            //定义并准备SQL语句
            String sql = "DELETE FROM user WHERE userid = ?";
            pstmt = conn.prepareStatement(sql);

            //给？赋值
            pstmt.setInt(1,userid);

            flag = pstmt.executeUpdate();
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            DbUtilHikari.close(pstmt,conn);
        }

        return flag;
    }

    /**
     * 修改用户信息
     * @param userid
     * @param username
     * @param upsw
     * @return 成功返回1， 否则返回0
     */
    public int updateUser(int userid, String username, String upsw){
        int flag = 0;

        //创建连接变量
        Connection conn = null;
        PreparedStatement pstmt = null;

        conn = DbUtilHikari.getConnection();

        String sql = "UPDATE user SET username = ? , userpswd = ? WHERE userid = ?";

        try {

            pstmt = conn.prepareStatement(sql);
            pstmt.setString(1,username);
            pstmt.setString(2,SecureUtil.md5(upsw));
            pstmt.setInt(3,userid);

            flag = pstmt.executeUpdate();
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {

            DbUtilHikari.close(pstmt,conn);
        }

        return flag;
    }

    /**
     * 查询所有的用户
     * @return：包含所有用户对象的集合
     */
    public List<User> selectAllUser(){
        //创建一个空集合，准备放用户对象
        List<User> userList = new ArrayList<User>();
        //创建连接时需要用到的变量
        Connection conn = null;
        PreparedStatement pstmt = null;
        ResultSet rs = null;

        //打开连接
        conn = DbUtilHikari.getConnection();

        //准备SQL语句
        String sql = "SELECT userid, username, userpsw FROM user";

        try {

            //执行SQL语句
            pstmt = conn.prepareStatement(sql);
            rs = pstmt.executeQuery();

            while (rs.next()){
                //将rs中的数据转移到
                User user = new User();
                user.setUserid(rs.getInt("userid"));
                user.setUsername(rs.getString("username"));
                user.setUserpsw(rs.getString("userpsw"));
                userList.add(user);
            }
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            DbUtilHikari.close(rs,pstmt,conn);
        }
        return userList;
    }
}